package com.wzy.未封装Jdbc;

import java.sql.*;
import java.util.Date;

public class Jdbc_news_detail {
    public static void main(String[] args) throws SQLException {
        Jdbc_news_detail jdbc2 = new Jdbc_news_detail();
        //根据id查询信息
//        jdbc2.selectById(4);
        //根据id修改信息
//        int author = jdbc2.updataById("dyx", 5);
//        if(author>0){
//            System.out.println("修改成功！");
//        }
        //根据id删除信息
        int r = jdbc2.deleteById(5);
        if(r>0){
            System.out.println("删除成功！");
        }

        //新增信息
//        int r = jdbc2.createNews(5,1,"JDBC连接MySQL","JDBC连接MySQL的六大步骤","1、导包  MySQL 驱动包；2、创建 驱动管理对象；3、创建SQL执行器；4、调用sql执行器里查询的方法；5、遍历输出sql执行器返回查询数据的结果集；6、关闭连接","222","admin",new Date(),new Date());
//        if(r>0){
//            System.out.println("新增成功！");
//        }
    }
    //新增信息
    public int createNews(int id,int categoryId, String title,String summary,String content ,String picPath,String author, Date date,Date date1) throws SQLException {
        int rows=0;
        Connection connection=null;
        PreparedStatement preparedStatement = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/newsdb?characterEncoding=UTF-8";
            String username = "root";
            String password = "wzy123518";
            connection=DriverManager.getConnection(url,username,password);
            String sql = "INSERT INTO `news_detail` (`id`, `categoryId`, `title`, `summary`, `content`, `picPath`, `author`, `createDate`, `modifyDate`) VALUES (?, ?, ?, ?, ?, ?, ?, ?,? )";
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            preparedStatement.setInt(2,categoryId);
            preparedStatement.setString(3,title);
            preparedStatement.setString(4,summary);
            preparedStatement.setString(5,content);
            preparedStatement.setString(6,picPath);
            preparedStatement.setString(7,author);
            preparedStatement.setTimestamp(8,new Timestamp(date.getTime()));
            preparedStatement.setTimestamp(9,new Timestamp(date.getTime()));
            rows = preparedStatement.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            preparedStatement.close();
            connection.close();
        }
        return rows;
    }

    //根据id删除信息
    public int deleteById(int id)throws  SQLException{
        int rows=0;
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://localhost:3306/newsdb?characterEncoding=UTF-8";
            String user = "root";
            String pass = "wzy123518";
            connection = DriverManager.getConnection(url, user, pass);
            //4、调用sql执行器里查询的方法
            String sql = "DELETE FROM `news_detail`  WHERE id=?";
            //3、创建SQL执行器
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);

            rows = preparedStatement.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //6、关闭连接
            preparedStatement.close();
            connection.close();
        }
        return rows;
    }

    //根据id修改信息
    public int updataById(String title, int id)throws  SQLException{
        int rows=0;
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://localhost:3306/newsdb?characterEncoding=UTF-8";
            String user = "root";
            String pass = "wzy123518";
            connection = DriverManager.getConnection(url, user, pass);
            //4、调用sql执行器里查询的方法
            String sql = "UPDATE `news_detail` SET title=? WHERE id=?";
            //3、创建SQL执行器
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,title);
            preparedStatement.setInt(2,id);

            rows = preparedStatement.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //6、关闭连接
            preparedStatement.close();
            connection.close();
        }
        return rows;
    }

    //根据id查询信息
    public  void selectById(int id) throws SQLException{
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url="jdbc:mysql://localhost:3306/newsdb?characterEncoding=UTF-8";
            String user = "root";
            String pass = "wzy123518";
            connection = DriverManager.getConnection(url, user, pass);
            //4、调用sql执行器里查询的方法
            String sql = "SELECT * FROM `news_detail` WHERE id=?";
            //3、创建SQL执行器
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            resultSet =  preparedStatement.executeQuery();

            //5、遍历输出sql执行器返回查询数据的结果集
            while(resultSet.next()){
                int id1 = resultSet.getInt(1);
                int categoryId = resultSet.getInt(2);
                String title = resultSet.getString(3);
                String summary = resultSet.getString(4);
                String content = resultSet.getString(5);
                String picPath = resultSet.getString(6);
                String author = resultSet.getString(7);
                java.util.Date date = resultSet.getDate(8);
                java.util.Date date1 = resultSet.getDate(9);
                System.out.println("id："+id1+"======推荐id："+categoryId+"======标题："+title+"======摘要："+summary+"======内容："+content+"======图片url："+picPath+"======作者："+author+"======创建日期："+date+"======上次修改日期："+date1);
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            //6、关闭连接
            resultSet.close();
            preparedStatement.close();
            connection.close();
        }
    }
}
